﻿//=====================================================================  	
//
//  File:       Program.cs
//
//  Summary:    This file contains a Control flow sample.
//
//  Date:       06/09/2005
//
//---------------------------------------------------------------------
//  This file is part of the Microsoft SQL Server Code Samples.
//  Copyright (C) Microsoft Corporation.  All rights reserved.
//
//  This source code is intended only as a supplement to Microsoft
//  Development Tools and/or on-line documentation.  See these other
//  materials for detailed information regarding Microsoft code samples.
//
//  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
//  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 
//  THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//  PARTICULAR PURPOSE.
//=====================================================================                         

#region Using directives

using System;
using System.IO;
using System.Collections.Generic;
using System.Text;

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Enumerators.SMO;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;

#endregion

namespace Microsoft.SqlServer.SSIS.Samples
{
    class Program
    {
        const string PACKAGEFILENAME = @"\SamplePackage.dtsx";

        public static void Main()
        {
            CreateForEachSmoEnumeratorPackageWithProperties();
        }

        private static void CreateForEachSmoEnumeratorPackageWithProperties()
        {
            string currentDirectory;
            Application application;
            Package package;
            Connections connections;
            ConnectionManager connectionManager;
            Executables executables;
            ForEachEnumeratorInfo forEachEnumeratorInfo;
            ForEachLoop forEachLoop;
            ForEachEnumeratorHost forEachEnumeratorHost;
            ForEachSMOEnumerator forEachSMOEnumerator;
            TaskHost scriptTaskHost;
            ScriptTask scriptTask;
            TaskHost executeSQLTaskHost;
            ExecuteSQLTask executeSQLTask;
            ForEachVariableMappings forEachVariableMappings;
            ForEachVariableMapping forEachVariableMapping;
            DTSExecResult status;

            // Create the SSIS application
            application = new Application();

            // Create the new package
            package = new Package();
            package.Properties["PackageType"].SetValue(package, 
                DTSPackageType.DTSDesigner90);
            package.Properties["Name"].SetValue(package, 
                @"SMO Enumerator Sample");
            package.Properties["Description"].SetValue(package, 
                @"This is a sample for SMO enumerators");
            package.Properties["CreatorComputerName"].SetValue(package, 
                @"MACHINENAME"); // System.Environment.MachineName;
            package.Properties["CreatorName"].SetValue(package, 
                @"USERNAME"); //System.Environment.UserName;

            // Add variables
            package.Variables.Add(@"SchemaName", false, @"User", 
                @"*** EMPTY ***");
            package.Variables.Add(@"TableName", false, @"User", 
                @"*** EMPTY ***");
            package.Variables.Add(@"TableObject", false, @"User", 
                new object());

            // Adds the ADO.NET connection manager to the package
            connections = package.Connections;
            connectionManager = connections.Add(@"ADO.NET:SQL");
            //@"ADO.NET:System.Data.SqlClient.SqlConnection, "
            //+ @"System.Data, "
            //+ @"Version=2.0.0.0, "
            //+ @"Culture=neutral, "
            //+ @"PublicKeyToken=b77a5c561934e089");
            Console.WriteLine(@"ConnectionManager creation name: "
                + connectionManager.Properties["CreationName"].GetValue(
                connectionManager).ToString());
            connectionManager.Properties["Name"].SetValue(connectionManager, 
                @"localhost.AdventureWorks");
            connectionManager.Properties["ConnectionString"].SetValue(
                connectionManager, @"Data Source=localhost;"
                + "Initial Catalog=AdventureWorks;"
                + "Integrated Security=True;");

            // Create ForEach SMO Loop task
            executables = package.Executables;
            forEachLoop = executables.Add(@"STOCK:FOREACHLOOP") as ForEachLoop;
            forEachLoop.Properties["Name"].SetValue(forEachLoop, 
                @"Foreach Loop Container");
            forEachLoop.Properties["Description"].SetValue(forEachLoop, 
                @"Foreach Loop Container");
            forEachVariableMappings = forEachLoop.VariableMappings;
            forEachVariableMapping = forEachVariableMappings.Add();
            forEachVariableMapping.VariableName = @"TableObject";
            forEachVariableMapping.ValueIndex = 0;

            Console.WriteLine(@"ForEachLoop creation name: "
                + forEachLoop.Properties["CreationName"].GetValue(
                forEachLoop).ToString());
            forEachEnumeratorInfo
                = application.ForEachEnumeratorInfos["Foreach SMO Enumerator"];
            forEachEnumeratorHost = forEachEnumeratorInfo.CreateNew();
            forEachLoop.ForEachEnumerator = forEachEnumeratorHost;

            // Setup the SMO enumerator
            forEachSMOEnumerator = forEachEnumeratorHost.InnerObject as ForEachSMOEnumerator;
            forEachSMOEnumerator.EnumURN = @"RuntimeServer[@Connection='"
                + connectionManager.ID + @"']/Server[@Name='localhost']/"
                + @"Database[@Name='AdventureWorks']"
                + @"/SMOEnumObj[@Name='Tables']"
                + @"/SMOEnumType[@Name='ObjectsPP']";

            // Add Script Task to ForEach Loop task
            scriptTaskHost = forEachLoop.Executables.Add(@"STOCK:SCRIPTTASK") as TaskHost;
            scriptTaskHost.Properties["Name"].SetValue(scriptTaskHost, 
                @"Script Task");
            scriptTaskHost.Properties["Description"].SetValue(scriptTaskHost, 
                @"Script Task");

            scriptTask = scriptTaskHost.InnerObject as ScriptTask;
            scriptTask.SetUniqueVsaProjectName();
            Console.WriteLine(@"VsaProjectName: " + scriptTask.VsaProjectName);
            scriptTask.CodeProvider.PutSourceCode(@"dts://Scripts/"
                + scriptTask.VsaProjectName + @"/ScriptMain.vsaitem",
                Properties.Resources.CodeFile);
            scriptTask.CodeProvider.PutSourceCode(@"dts://Scripts/"
                + scriptTask.VsaProjectName + @"/" + scriptTask.VsaProjectName 
                + @".vsaproj",
                Properties.Resources.ProjectFile);
            scriptTask.ReadOnlyVariables = @"User::TableObject";
            scriptTask.ReadWriteVariables = @"User::SchemaName,User::TableName";
            scriptTask.PreCompile = false;

            // Add Execute SQL Task to ForEach Loop task
            executeSQLTaskHost = forEachLoop.Executables.Add(@"STOCK:SQLTASK") as TaskHost;
            executeSQLTaskHost.Properties["Name"].SetValue(executeSQLTaskHost, 
                @"DBCC CHECK CONSTRAINTS");
            executeSQLTaskHost.Properties["Description"].SetValue(executeSQLTaskHost, 
                @"Execute SQL Task");
            executeSQLTaskHost.SetExpression(@"SqlStatementSource", 
                Properties.Resources.SqlStatement);
            executeSQLTask = executeSQLTaskHost.InnerObject as ExecuteSQLTask;
            executeSQLTask.Connection = connectionManager.Name;
            executeSQLTask.SqlStatementSource = @"*** See Expressions ***";
            executeSQLTask.SqlStatementSourceType 
                = SqlStatementSourceType.DirectInput;

            // Add precedence constraint from script task to execute SQL task
            forEachLoop.PrecedenceConstraints.Add(scriptTaskHost, executeSQLTaskHost);

            // Validate the layout of the package
            status = package.Validate(package.Connections, null, 
                new PackageEvents(), null);
            Console.WriteLine(@"Validation result: " + status);

            // Save the package
            currentDirectory = Environment.CurrentDirectory;
            application.SaveToXml(currentDirectory + PACKAGEFILENAME, package, null);
            Console.WriteLine(@"Package saved to " + currentDirectory + PACKAGEFILENAME);
        }
    }
}
